<?php

class Model_Manager extends Model
{

    public function getItemsLimit($section, $limit=6, $order = 'name', $orderDir = 'ASC') {
        $items = $this->db->query('SELECT * FROM `directoryItems` WHERE `section`=? AND `isCategory` = 0 ORDER BY `' . $order . '` ' . $orderDir . ' LIMIT ' . $limit, $section)->fetchAll();
        $result = array();
        foreach ($items as $item) {
            $result[$item['id']] = $item;
        }
        return $result;
    }
    public function getItemsLimitParrentAlias($section, $limit=6, $order = 'name', $orderDir = 'ASC') {
        $items = $this->db->query('SELECT * FROM `directoryItems` WHERE `section`=? AND `isCategory` = 0 ORDER BY `' . $order . '` ' . $orderDir . ' LIMIT ' . $limit, $section)->fetchAll();
        $result = array();
        foreach ($items as $item) {
            $result[$item['id']] = $item;
        }
        return $result;
    }
    public function getChidrenCategoriesByAlias($alias, $section, $order = 'name', $orderDir = 'ASC') {
        $categories = $this->db->query('SELECT * FROM `directoryItems` WHERE `section`=? AND `alias`=? AND `isCategory` = 1 ORDER BY `' . $order . '` ' . $orderDir, array($section, $alias))->fetch();
        return $this->db->query('SELECT * FROM `directoryItems` WHERE `parentId`=? AND `section`=? AND `isCategory` = 1 ORDER BY `' . $order . '` ' . $orderDir, array($categories['id'], $section))->fetchAll();
    }
    public function getCategoryItemsByAlias($alias, $section, $page, $limit=8) {
        $startFrom=$page*$limit;
        $categoryData = $this->db->query('SELECT * FROM `directoryItems` WHERE `alias`=? AND `section`=? AND `isCategory` = 1 LIMIT 1', array($alias, $section))->fetch();
        $items = $this->db->query('SELECT * FROM `directoryItems` WHERE `parentId`=? AND `section`=? AND `isCategory` = 0 LIMIT ' . $startFrom . ',' . $limit, array($categoryData['id'], $section))->fetchAll();
//        system::dump($categoryData);exit();
        $result = array();
        foreach ($items as $item) {
            $result[] = $item;
        }
        return $result;
    }
    public function paginatorNavigator($categoryId, $section, $limit=6, $page=0, $link=NULL) {
        $startFrom=$page*$limit;
        $countItems = $this->db->query('SELECT count(*) as allItemsCount FROM `directoryItems` WHERE `parentId`=? AND `section`=? AND `isCategory` = 0', array($categoryId, $section))->fetch();
        $allPages = ceil($countItems['allItemsCount'] / $limit);
        if ($link == NULL) {
            return $allPages;
        }
        $paginatorData = '<div class="shop-paginator">';
        for ($i = 1; $i <= $allPages; $i++) {
            if ($i - 1 == $page) {
                $paginatorData .= $i . " \n";
            } else {
                $paginatorData .= '<a href="'.$link.'/'.$i.'" class="shop-paginator-page">'.$i."</a> \n";
            }
        }if($allPages<=1)$paginatorData='';
        $paginatorData .= '</div>';
        return $paginatorData;
    }
    public function getColors($section) {
        $images = $this->db->query('SELECT * FROM `directoryImages` WHERE `section`=? ORDER BY `name` ASC', $section)->fetchAll();
        $result = array();
        foreach ($images as $image) {
            $result[$image['name']] = $image;
        }
        return $result;
    }
    public function getSizes($section) {
        $images = $this->db->query('SELECT * FROM `directoryImages` WHERE `section`=? ORDER BY `name` ASC', $section)->fetchAll();
        $result = array();
        foreach ($images as $image) {
            $result[$image['name']] = $image;
        }
        return $result;
    }
    public function getImagesParrentKey($section) {
        $images = $this->db->query('SELECT * FROM `directoryImages` WHERE `section`=? ORDER BY `itemId` ASC', $section)->fetchAll();
        $result = array();
        $image=array();
        foreach ($images as $image) {
            $result[$image['itemId']][] = $image;
        }
        return $result;
    }

    public function getImageForCartUrl($itemId)
    {
        $image = $this->db->query('SELECT * FROM `directoryImages` WHERE `itemId`=? ORDER BY `itemId` ASC', $itemId)->fetch();
        
        if(!empty($image))
            {
                return '/content/directory/images/shop/shopItemInCart/'.substr($image['alias'],0,1).'/'.$image['alias'].'.jpg';
            }
          else
          {
              return '/images/cart_noimg.png';
          }
    }
    public function selectUser($userId) {
        return $this->db->query('SELECT * FROM `users` WHERE `id`=? LIMIT 1', $userId)->fetch();
       // system::dump($this->db->query('SELECT * FROM `users` WHERE `id`=? LIMIT 1', $userId)->fetch());exit();
    }

    public function selectMember($userId,$name1=null,$name2=null) {
        $data=$this->db->query('SELECT * FROM `members` WHERE `user_id`=? LIMIT 1', $userId)->fetch();
        if($data==null){
            $this->db->query('INSERT INTO `members` (`user_id`,`name1`,`name2`)VALUES("'.$userId.'","'.$name1.'","'.$name2.'")')->insertId();
            $data=$this->db->query('SELECT * FROM `members` WHERE `user_id`=? LIMIT 1', $userId)->fetch();
        }
        return $data;
    }
    public function setMember($userId,$name1=null,$name2=null,$gender=null) {
        return $this->db->query('UPDATE `members` SET `name1`=?,`name2`=?,`gender`=? WHERE `user_id`=?', array($name1,$name2,$gender,$userId))->insertId();
    }

    //functions for texts
    public function getText($category)
    {
       $parentId = $this->db->query("SELECT `id` FROM `directoryItems` WHERE `name`=? AND `isCategory`=1 ",$category)->fetch();
       return $this->db->query('SELECT * FROM `directoryItems` WHERE `parentId` = ?',$parentId['id'])->fetchAll();
    }

    public function getShipping($userId) {
        return $this->db->query('SELECT * FROM `shippingAdress` WHERE `user_id`=?', $userId)->fetchAll();
    }
    public function addShipping($userId,$name1,$name2,$country,$street,$city,$state,$postal,$phone,$primary) {
        return$this->db->query('INSERT INTO `shippingAdress` (`user_id`,`name1`,`name2`,`country`,`street`,`city`,`state`,`postal`,`phone`,`primary`)VALUES("'.$userId.'","'.$name1.'","'.$name2.'","'.$country.'","'.$street.'","'.$city.'","'.$state.'","'.$postal.'","'.$phone.'","'.$primary.'")')->insertId();
    }
    public function updateShipping($id,$userId,$name1,$name2,$country,$street,$city,$state,$postal,$phone,$primary) {
        return$this->db->query('UPDATE `shippingAdress` SET `name1`=?,`name2`=?,`country`=?,`street`=?,`city`=?,`state`=?,`postal`=?,`phone`=?
        WHERE `id`=? AND `user_id`=?', array($name1,$name2,$country,$street,$city,$state,$postal,$phone,$id,$userId))->insertId();
    }
    public function getShippingId($id,$userId) {
        return $this->db->query('SELECT * FROM `shippingAdress` WHERE `id`=? AND `user_id`=?  LIMIT 1', array($id,$userId))->fetch();
    }
    public function setPrimaryShipping($id,$userId) {
        $this->db->query('UPDATE `shippingAdress` SET `primary`=?
        WHERE `user_id`=?', array('0',$userId))->insertId();
        return$this->db->query('UPDATE `shippingAdress` SET `primary`=?
        WHERE `id`=? AND `user_id`=?', array('1',$id,$userId))->insertId();
    }
    public function dropShipping($id,$userId) {
        return $this->db->query('DELETE FROM `shippingAdress`WHERE `id`=? AND `user_id`=? LIMIT 1', array($id,$userId))->insertId();
    }
    public function getPrimary($userId) {
        return $this->db->query('SELECT * FROM `shippingAdress` WHERE `user_id`=? AND `primary`=1', $userId)->fetchAll();
    }

    
    public function getMainPageImage($position)
    {
        $itemId = $this->db->query("SELECT * FROM `directoryItems` WHERE `section`= 'main' LIMIT 1")->fetch();
        
        $image = $this->db->query('SELECT * FROM `directoryImages` WHERE `itemId`=? AND `select1` = ? ORDER BY `itemId` ASC', array($itemId['id'],$position) )->fetch();
        
        switch ($image['select2']) 
        {
            case 0:
                $image['url'] = '/shop/category/women/';
                break;
            case 1:
                $image['url'] = '/shop/category/men/';
                break;
            case 2:
                $image['url'] = '/shop/category/kids/';
                break;
            case 3:
                $image['url'] = '/shop/category/accessories/';
                break;
        }
        if(isset($image['select1'])){
        switch ($image['select1']) 
        {
            case 0:
                $size = 'first_index';
                break;
            case 1:
                $size = 'second_index';
                break;
            case 2:
                $size = 'third_index';
                break;
            case 3:
                $size = 'fourth_index';
                break;
            default:
                $size = 'fourth_index';
                break;
        }
        
        $image['src'] = $this->imagePath($image['section'], $size, $image['alias']);}
        
        return $image;
    }
    
    public function imagePath($section, $size,$alias, $extension = 'jpg')
    {
            return '/content/directory/images/' . $section . '/' . $size . '/' . substr($alias, 0, 1) . '/' . $alias . '.' . $extension;
    }
    
    public function getSuggestions($id,$parentId)
    {
        $suggestions =  $this->db->query("SELECT * FROM `directoryItems` WHERE `parentId`=? AND `id` <>? AND `checkbox2`=1 ORDER BY RAND() LIMIT 4",array($parentId,$id))->fetchAll();
         
        foreach ($suggestions as $key => $value) {
            $image = $this->db->query("SELECT * FROM `directoryImages` WHERE `itemId`=?",$suggestions[$key]['id'])->fetch();
           $suggestions[$key]['img'] = $this->imagePath($image['section'], 'shopItemRecent',$image['alias']);
        }
       return $suggestions;
    }
    public function getInvitations($uid){
        return $this->db->query('SELECT * FROM `invitations` WHERE `uid` = ?',$uid)->fetchAll();
    }
    public function newInvitation($uid,$mail){
        $checkMail=$this->db->query('SELECT * FROM `invitations` WHERE `mail` = ? LIMIT 1',$mail)->fetch();
        $checkRegistered=$this->db->query('SELECT * FROM `users` WHERE `email` = ? LIMIT 1',$mail)->fetch();
        if(empty($checkMail)&&empty($checkRegistered)){
            $key=md5(rand(0,2000));
            $status=$this->db->query('INSERT INTO `invitations` (`uid`,`mail`,`link`,`status`)VALUES("'.$uid.'","'.$mail.'","'.$key.'","0")')->insertId();
            $msg='';
            $msg.='Hello dear friend'."\n";
            $msg.='You friend got your link to register at our system'."\n";
            $msg.='http://'.$_SERVER['HTTP_HOST'].'/invite/index/'.$mail.'/'.$key.'/'."\n";
            $rez = Mailer::sendMail($mail, 'Message from ' . $_SERVER['HTTP_HOST'], $msg, 'KASARIE-KOSMI'.'<noreply@'.$_SERVER['HTTP_HOST'].'>');
            return $status;
        }else{
            return false;
        }
    }
    public function confirmInvitation($mail,$key){
        $ans=$this->db->query('SELECT * FROM `invitations` WHERE `mail` = ? AND `link` = ? AND `status`=0 LIMIT 1',array($mail,$key))->fetch();
        if($ans<>null){
            $this->db->query('UPDATE `invitations` SET `status`=?
            WHERE `id`=?', array('1',$ans['id']))->insertId();
        }
    }
    public function getOrders($mail){
        return$this->db->query('SELECT * FROM `cartOrders` WHERE `email` = ?',$mail)->fetchAll();
    }
    public function getOrderId($hash){
        $id=substr($hash, 4, -3);
        return$this->db->query('SELECT * FROM `cartItems` WHERE `orderId` = ?',$id)->fetchAll();
    }
    public function updateCredits($uid,$umail){
        $member=$this->db->query('SELECT * FROM `members` WHERE `user_id`=? LIMIT 1', $uid)->fetch();
        $confirmed=$this->db->query('SELECT * FROM `invitations` WHERE `uid` = ? AND `status` = ?',array($uid,'1'))->fetchAll();
        if($confirmed){
        foreach($confirmed as $conf){
            $payed=$this->db->query('SELECT * FROM `cartOrders` WHERE `isPaid` = 1 AND `email` = ?',$conf['mail'])->fetchAll();
            if($payed){
                $this->db->query('UPDATE `invitations` SET `status`=?
                WHERE `id`=?', array('2',$conf['id']))->insertId();
                $member['ballance']+=25;
            }
        }
            return$this->db->query('UPDATE `members` SET `ballance`=?
                   WHERE `id`=?', array($member['ballance'],$member['id']))->insertId();
        }
    }
    public function getBonusAmount($userId)
    {
       $balans= $this->db->query("SELECT `ballance` FROM `members` WHERE `user_id`=?",$userId)->fetch();
       return $balans['ballance'];
    }
    public function updateAccountBonusAmount($amount,$id)
    {
//        $this->db->query("UPDATE `members` SET `ballance`='`ballance` - {$amount}' WHERE `user_id`=?",$id);
//        return;
        $this->db->query("UPDATE members SET ballance=ballance -{$amount} WHERE user_id={$id}");
        return;
    }
    public function getVoteImgs($id)
    {
        $this->db->query('SELECT * FROM `cartItems` WHERE `orderId` = ?',$id)->fetchAll;
    }

    public function getImages($section)
    {
        $images = $this->db->query('SELECT * FROM `directoryImages` WHERE `section`=? ORDER BY `position` ASC', $section)->fetchAll();
        $result = array();
        foreach ($images as $image) {
            $result[$image['id']] = $image;
        }
        return $result;
    }
    public function addVote($userId,$pictureId)
    {
        $distinct = $this->db->query("SELECT * FROM `votes` WHERE `user_id`=? AND `picture_id`=?",array($userId,$pictureId))->fetch();
        if(empty($distinct))
        {
            $this->db->query('INSERT INTO `votes` (`user_id`,`picture_id`)VALUES("'.$userId.'","'.$pictureId.'")')->insertId();
            $votesForPicture = $this->db->query("SELECT * FROM `votes` WHERE `picture_id`=?",$pictureId)->count();
            return $votesForPicture;
        }
        else 
            return FALSE;
    }

    public function checkVoted($userId,$imageId){
        $check = $this->db->query("SELECT * FROM `votes` WHERE `user_id`=? AND `picture_id`=?",array($userId,$imageId))->fetch();
        if(empty($check)){
            return FALSE;
        }
        else
            return TRUE;
    }
    
    public function countVotes($pictureId)
    {
        return $this->db->query("SELECT * FROM `votes` WHERE `picture_id`=?",$pictureId)->count();
    }

    public function getImgVotes($img_id)
    {
        return $this->db->query("SELECT * FROM `votes` WHERE `picture_id`=?",$img_id)->count();
    }
    
    public function getSocial($key)
    {
        $social = $this->db->query("SELECT `value` FROM `settings` WHERE `key`=?",$key)->fetch();
        return $social['value'];
    }
    public function getUserName($id){
        $user = $this->db->query("SELECT `name1` FROM `members` WHERE `user_id`=?",$id)->fetch();
        return $user['name1'];
    }
    public function addFavouriteSize($userId,$size){
        $result = $this->db->query("UPDATE `members` SET `size`={$size} WHERE user_id={$userId}");
        return $result;
    }
    public function getFavSize($userId){
        $user = $this->db->query("SELECT `size` FROM `members` WHERE `user_id`=?",$userId)->fetch();
        return $user['size'];
    }
    public function getTestimonials($limit=20){
        return $this->db->query("SELECT * FROM `directoryItems` WHERE `section`='indextestimonials' LIMIT ?",$limit)->fetchAll();
    }

    public function getTestimonialsWithLimit($page,$limit){

        $startFrom=$page*$limit;
        $items = $this->db->query('SELECT * FROM `directoryItems` WHERE `section`="indextestimonials" LIMIT ' . $startFrom . ',' . $limit)->fetchAll();
        return $items;
    }
    public function getTestimonialsIndex($section, $order = 'name', $limit=20, $orderDir = 'ASC')
    {
        $items = $this->db->query('SELECT * FROM `directoryItems` WHERE `section`=? AND `isCategory` = 0 AND `checkbox1` = 1 ORDER BY `' . $order . '` ' . $orderDir.' LIMIT '.$limit, $section)->fetchAll();
        $result = array();
        foreach ($items as $item) {
            $result[$item['id']] = $item;
        }
        return $result;
    }


    public function addView($postAlias) {
        $post = $this->db->query("SELECT `id` FROM `directoryItems` WHERE `alias`=?",$postAlias)->fetch();
        $postId = $post['id'];

        $int_view = $this->db->query('SELECT * FROM `blog_views` WHERE `item_id`=?',$postId)->count();
        if($int_view > 0){
            $this->db->query("UPDATE `blog_views` SET `views`=`views`+1 WHERE `item_id`=?",$postId);
        }
        else{
            $this->db->query('INSERT INTO `blog_views` (`item_id`,`views`)VALUES("'.$postId.'","1")')->insertId();
        }
        return;
    }

    public static function getViews($post_id){
        $db = Database::instance();
        $int_views = $db->query('SELECT `views` FROM `blog_views` WHERE `item_id`=?',$post_id)->fetch();
        return $int_views['views'];
    }

    public static function addComment($author,$authorId,$comment,$postId){
        $db = Database::instance();
        return $db->query("INSERT INTO `blog_comments` (`id`,`author`,`author_id`,`text`,`time`,`hasReplies`,`isReply`,`parent_id`,`post_id`) VALUES ('','".$author."','".$authorId."','".$comment."',NOW(),'','','','".$postId."')")->insertId();
    }

    public static function addReply($author,$authorId,$comment,$postId,$parentId){
        $db = Database::instance();
        $result= $db->query("INSERT INTO `blog_comments` (`id`,`author`,`author_id`,`text`,`time`,`hasReplies`,`isReply`,`parent_id`,`post_id`) VALUES ('','".$author."','".$authorId."','".$comment."',NOW(),'','1','".$parentId."','".$postId."')")->insertId();
        if($result){
            $db->query("UPDATE `blog_comments` SET `hasReplies`=1 WHERE `id`=?",$parentId)->insertId();
        }
        return $result;
    }

    public static function getCommentById($id){
        $db = Database::instance();
        return $comments = $db->query("SELECT * FROM `blog_comments` WHERE `id`=?",$id)->fetch();
    }


    public static function getCommentsByPostId($postId){
        $db = Database::instance();
        return $comments = $db->query("SELECT * FROM `blog_comments` WHERE `post_id`=? ORDER BY ID DESC",$postId)->fetchAll();
    }

    public static function getCommentsByPostIdWithLimit($postId,$order = 'id', $orderDir = 'DESC',$page,$limit){
        $db = Database::instance();
        $startFrom = $limit*$page;

        $items = $db->query('SELECT * FROM `blog_comments` WHERE `post_id`=? AND `isReply` = 0  ORDER BY `' . $order . '` ' . $orderDir .' LIMIT ' . $startFrom . ',' . $limit, array($postId))->fetchAll();
        $result = array();
        foreach ($items as $item) {
            $result[$item['id']] = $item;
        }
        return $result;
    }

    public static function getReplies($commentsIds){
        $db = Database::instance();
        return $db->query('SELECT * FROM `blog_comments` WHERE `parent_id` IN('.$commentsIds.') ORDER BY `id` DESC')->fetchAll();
    }




    public static function deleteComment($id){
        $db = Database::instance();
        $commCheck = $db->query("SELECT * FROM `blog_comments` WHERE `id`=?",$id)->fetch();
        if($commCheck['isReply'] == 0 && $commCheck['hasReplies'] == 1){
            $db->query('DELETE FROM `blog_comments` WHERE `parent_id` = ?',$commCheck['id']);
        }
        $result = $db->query('DELETE FROM `blog_comments`WHERE `id`=?',$id);
        if($result)
            return true;
        else
            return false;
    }
    public function getCategoryItemsWithLimit($categoryId, $section, $order = 'name', $orderDir = 'ASC',$page,$limit){
        $startFrom = $limit*$page;
        $items = $this->db->query('SELECT * FROM `directoryItems` WHERE `parentId`=? AND `section`=? AND `isCategory` = 0 ORDER BY `' . $order . '` ' . $orderDir .' LIMIT ' . $startFrom . ',' . $limit, array($categoryId, $section))->fetchAll();
        $result = array();
        foreach ($items as $item) {
            $result[$item['id']] = $item;
        }
        return $result;
    }


}

